package br.com.rafaelSys.importacao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;

import br.com.rafaelSys.importacao.entidades.Ativo;
import br.com.rafaelSys.importacao.entidades.AtivoSerie;


public class AtivoDao {


	public Connection conecta(){
		Connection conn = null;

		try
		{
			String userName = "root";
			String password = "masterkey";
			String url = "jdbc:mysql://localhost/rafaelSys";
			Class.forName ("com.mysql.jdbc.Driver").newInstance ();
			conn = (Connection) DriverManager.getConnection (url, userName, password);
			//			System.out.println ("Database connection established");
		}
		catch (Exception e)
		{
			System.err.println ("Cannot connect to database server");
			e.printStackTrace();
		}
		return conn;
	}

	public Ativo persisteAtivo(Ativo ativoNovo){

		String sql = "INSERT INTO rafaelSys.ATIVO (simbolo, nome, data_inicial, data_atualizacao) VALUES(?, ?, ?, ?)";
		Connection connection = conecta();
		PreparedStatement pstmt = null;
		Ativo ativo = null;


		ativo = buscaAtivo(ativoNovo.getSimbolo());

		if(ativo == null){
			try {
				pstmt = connection.prepareStatement(sql);

				pstmt.setString(1, ativoNovo.getSimbolo());
				pstmt.setString(2, ativoNovo.getNome());

				if(ativoNovo.getInicial() != null)
					pstmt.setDate(3, new java.sql.Date(ativoNovo.getInicial().getTime()));
				else
					pstmt.setDate(3, null);
				
				if(ativoNovo.getAtualizacao() != null)
					pstmt.setDate(4, new java.sql.Date(ativoNovo.getAtualizacao().getTime()));
				else
					pstmt.setDate(4, null);
				
				pstmt.executeUpdate();


			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		else
			return ativo;

		ativo = buscaAtivo(ativoNovo.getSimbolo());

//		System.out.println(ativo.getId() + "\t" + ativo.getSimbolo());
		return ativo;
	}

	public Ativo buscaAtivo(String simbolo){

		Ativo ativo = null;
		ResultSet rs = null;
		Statement s = null;
		Date inicial = null;

		Date atualizacao = null;
		try {
			Connection conn = conecta();
			s = (Statement) conn.createStatement();
			s.executeQuery ("SELECT * FROM rafaelSys.ATIVO m WHERE m.simbolo = '" + simbolo + "'");
			rs = s.getResultSet ();
			int count = 0;
			while (rs.next ())
			{
				int id = rs.getInt("id");
				String nome = rs.getString("nome");
				String simboloB = rs.getString("simbolo");
				
				java.sql.Date iniSql = rs.getDate("data_inicial");
				if(iniSql != null)
				inicial = new Date(iniSql.getTime());
				
				java.sql.Date fimSql = rs.getDate("data_atualizacao");
				if(fimSql != null)
				atualizacao = new Date(fimSql.getTime());
				
				ativo = new Ativo(simboloB, nome, inicial, atualizacao);
				ativo.setId(id);
				++count;
			}
			rs.close ();
			s.close ();
		}
		catch (SQLException e1) {
			e1.printStackTrace();
		}
		//		System.out.println (count + " rows were retrieved");
		return ativo;
	}

	public void atualizaAtivo(Ativo ativo){

		String sql = "UPDATE rafaelSys.ATIVO SET nome = ?, simbolo = ?, data_inicial = ?, data_atualizacao = ? WHERE id = ?";
		Connection connection = conecta();
		PreparedStatement pstmt = null;

		try {
			pstmt = connection.prepareStatement(sql);

			pstmt.setString(1, ativo.getNome());
			pstmt.setString(2, ativo.getSimbolo());
			
			if(ativo.getInicial() != null)
				pstmt.setDate(3, new java.sql.Date(ativo.getInicial().getTime()));
			else
				pstmt.setDate(3, null);
			
			if(ativo.getAtualizacao() != null)
				pstmt.setDate(4, new java.sql.Date(ativo.getAtualizacao().getTime()));
			else
				pstmt.setDate(4, null);
			
			pstmt.setInt(5, ativo.getId());

			pstmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void persisteAtivoSerie(AtivoSerie serieNova) throws SQLException{

		String sql = "INSERT INTO rafaelSys.ATIVO_SERIE (ativo_id, data, open, high, low, close, volume, adj)" +
		" VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
		Connection connection = conecta();
		PreparedStatement pstmt = null;
		SimpleDateFormat df = new SimpleDateFormat("yyyy-mm-dd");

		try {
			pstmt = connection.prepareStatement(sql);

			pstmt.setInt(1, serieNova.getAtivo().getId());
			pstmt.setString(2, df.format(serieNova.getData()));
			pstmt.setDouble(3, serieNova.getOpen());
			pstmt.setDouble(4, serieNova.getHigh());
			pstmt.setDouble(5, serieNova.getLow());
			pstmt.setDouble(6, serieNova.getClose());
			pstmt.setLong(7, serieNova.getVolume());
			pstmt.setDouble(8, serieNova.getAdj());

			pstmt.executeUpdate();


		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			connection.close();
		}

		//		System.out.println(" --- > " + serieNova);
	}
}
